import sqlite3

def convert(value):
	if value.startswith('~'):
		return value.strip('~')
	if not value:
		value = '0'
	return float(value)

coon = sqlite3.connect('food.db')
curs = coon.cursor()
curs.execute('''
	create table food(
	id text primary key,
	desc text,
	water float,
	kcal float,
	protein float,
	fat float,
	ash float,
	carbs float,
	fiber float,
	sugar float
	)
	''')
query = 'insert into food values(?,?,?,?,?,?,?,?,?,?)'
for line in open('/Users/mac/Desktop/ABBREV.txt'):
	fields = line.split('^')
	field_count = fields.count
	vals = [convert(f) for f in fields[:field_count]]
	curs.execute(query.vals)

coon.commit()
coon.close